# The following resets the data for use with the update section
# For both tables:
#  Table data is removed.
#  The identity column is set to start with the id after the starting data.
#  Using the executemany function an array of starting data is inserted into the table.
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
#  section titled "Resetting the data"

require 'oci8'
connectString = ENV['DB_CONNECT'] # The environment variable for the connect string: DB_CONNECT=user/password@database
con = OCI8.new(connectString)

# Delete rows
cursor = con.parse('delete from lcs_pets')
cursor.exec

# Reset Identity Coulmn
cursor = con.parse('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 8)')
cursor.exec

# Delete rows
cursor = con.parse('delete from lcs_people')
cursor.exec

# Reset Identity Coulmn
cursor = con.parse('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 8)')
cursor.exec

# Insert default people rows
cursor = con.parse('INSERT INTO lcs_people(id, name, age, notes) VALUES (:id, :name, :age, :notes)')
cursor.max_array_size = 7
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
cursor.bind_param_array(:name, %w[Bob Kim Cheryl Bob Stacey Pete Pat])
cursor.bind_param_array(:age, [35, 27, 23, 27, 45, 23, 36])
cursor.bind_param_array(:notes, ['I like dogs', 'I like birds', 'I like horses', 'I like rabbits', 'I like snakes', 'I like cats', 'I like dogs'])
people_row_count = cursor.exec_array
printf " %d people rows inserted\n", people_row_count

# Insert default pet rows
cursor = con.parse('INSERT INTO lcs_pets(id, name, owner, type) VALUES (:id, :name, :owner, :type)')
cursor.max_array_size = 7
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
cursor.bind_param_array(:name, %w[Duke Dragon Sneaky Red Red Buster Fido])
cursor.bind_param_array(:owner, [1, 2, 5, 2, 3, 1, 7])
cursor.bind_param_array(:type, %w[dog bird snake bird horse dog cat])
pet_row_count = cursor.exec_array
printf " %d pet rows inserted\n", pet_row_count

con.commit
